﻿<?xml version="1.0" encoding="utf-8"?><Articles><Article><ArticlesID>605</ArticlesID><Title>Quản l&amp;#253; Indexes trong SQL Server 2005</Title><Body>&amp;lt;p&amp;gt;If you are using the new SQL Server 2005, you've to remember that the  instructions &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;DBB SHOWCONTIG&amp;lt;/font&amp;gt;, &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;DBCC DBREINDEX&amp;lt;/font&amp;gt;, &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;DBCC  INDEXDEFRAG&amp;lt;/font&amp;gt; are now deprecated (actually you can use them but their  support is not guaranteed on future versions). On SQL Server 2005 you have the  new function &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;ALTER INDEX&amp;lt;/font&amp;gt; for all index  management functions.&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;Here a brief summary of all these new functions (referred to my previous  example):&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;1) To view size and fragmentation information, you have to use the &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;sys.dm_db_index_physical_stats&amp;lt;/font&amp;gt; catalog view. For  example:&amp;lt;/p&amp;gt;
&amp;lt;pre title=&amp;quot;code&amp;quot; class=&amp;quot;brush: sql;&amp;quot;&amp;gt;
USE Northwind;
SELECT * FROM sys.dm_db_index_physical_stats&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;&amp;lt;br /&amp;gt;
&amp;lt;/font&amp;gt;&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;2) To handle index fragmentation, you have to use the &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;ALTER INDEX&amp;lt;/font&amp;gt; instruction with the &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;REORGANIZE&amp;lt;/font&amp;gt; keyword. For example:&amp;lt;/p&amp;gt;
&amp;lt;pre title=&amp;quot;code&amp;quot; class=&amp;quot;brush: sql;&amp;quot;&amp;gt;
ALTER INDEX PK_Order_Details ON 'Order Details'
REORGANIZE&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;&amp;lt;br /&amp;gt;
&amp;lt;/font&amp;gt;3) To rebuild an index on a table,&amp;amp;nbsp;you have to&amp;amp;nbsp;use the &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;ALTER INDEX&amp;lt;/font&amp;gt; instruction with the&amp;amp;nbsp;&amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;REBUILD&amp;lt;/font&amp;gt; keyword. For example:&amp;lt;/p&amp;gt;
&amp;lt;pre title=&amp;quot;code&amp;quot; class=&amp;quot;brush: sql;&amp;quot;&amp;gt;
ALTER INDEX PK_Order_Details ON 'Order Details'
REBUILD&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;You can use this instruction also for re-enable a previously disabled  index.&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;4) To rebuild all indexes on a table,&amp;amp;nbsp;you can&amp;amp;nbsp;use the &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;ALL&amp;lt;/font&amp;gt; keyword. For example:&amp;lt;/p&amp;gt;
&amp;lt;pre title=&amp;quot;code&amp;quot; class=&amp;quot;brush: sql;&amp;quot;&amp;gt;
ALTER INDEX ALL ON 'Order Details'
REBUILD&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;&amp;lt;br /&amp;gt;
&amp;lt;/font&amp;gt;&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;5) To disable an index, you can use the &amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;ALTER  INDEX&amp;lt;/font&amp;gt; instruction with the&amp;amp;nbsp;&amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;DISABLE&amp;lt;/font&amp;gt;  keyword. For example:&amp;lt;/p&amp;gt;
&amp;lt;pre title=&amp;quot;code&amp;quot; class=&amp;quot;brush: sql;&amp;quot;&amp;gt;
ALTER INDEX PK_Order_Details ON 'Order Details'
DISABLE&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;lt;font face=&amp;quot;Courier New&amp;quot;&amp;gt;&amp;lt;br /&amp;gt;
&amp;lt;/font&amp;gt;&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;I think that SQL Server 2005 introduces a more elegant and centralized way of  index management.&amp;lt;/p&amp;gt;</Body><CommentEnabled>True</CommentEnabled><ViewCount>0</ViewCount><ReleaseDate>11/11/2009 10:28:20 PM</ReleaseDate><TotalComment>0</TotalComment><IsMedia>False</IsMedia><IsPicture>False</IsPicture><IsVote>True</IsVote><ArticlePassword /><PostVisible>0</PostVisible><ArticlePath>/2009/11/11/Quan-ly-Indexes-trong-SQL-Server-2005-25D</ArticlePath><Approved>True</Approved><ImagePath /><CategoryId>35</CategoryId><ArrayCatID>35|25</ArrayCatID></Article></Articles>